package com.wutong.eams.utils;

import com.wutong.eams.config.DatabaseConfig;
import com.wutong.eams.exception.DatabaseException;
import com.wutong.eams.other.ConditionBuilder;
import lombok.extern.slf4j.Slf4j;

import java.lang.reflect.Field;
import java.sql.*;
import java.time.LocalDateTime;
import java.util.*;
import java.util.Date;
import java.util.concurrent.atomic.AtomicInteger;

@Slf4j
public class DatabaseUtil {

    private DatabaseUtil() {
    } // 防止实例化

    public static <T> List<T> resultSetToObject(ResultSet resultSet, Class<T> entityClass, boolean ignoreNull) {
        List<T> result = new ArrayList<>();
        try (resultSet) {
            while (resultSet.next()) {
                T entity = entityClass.getConstructor().newInstance();
                Field[] fields = entityClass.getDeclaredFields();
                for (Field field : fields) {
                    field.setAccessible(true);
                    String column = StringUtil.convertUnderline(field.getName());
                    Object value = resultSet.getObject(column);
                    setFieldValue(entity, field, value, ignoreNull);
                }
                result.add(entity);
            }
            return result;
        } catch (Exception e) {
            throw handleException("将ResultSet转换为实体类失败", e);
        }
    }

    public static <T> int insert(String tableName, T entity) {
        String sql = buildInsertSql(tableName, entity);
        try (Connection conn = DatabaseConfig.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {

            setParameters(stmt, getNonNullFieldValues(entity));
            int affectedRows = stmt.executeUpdate();

            if (affectedRows > 0) {
                try (ResultSet generatedKeys = stmt.getGeneratedKeys()) {
                    if (generatedKeys.next()) {
                        return generatedKeys.getInt(1);
                    }
                }
            }
            return affectedRows;
        } catch (Exception e) {
            throw handleException("插入数据失败", e);
        }
    }

    public static <T> int update(String tableName, T entity, ConditionBuilder conditionBuilder) {
        String sql = buildUpdateSql(tableName, entity, conditionBuilder);
        try (Connection conn = DatabaseConfig.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            return stmt.executeUpdate();
        } catch (Exception e) {
            throw handleException("更新数据失败", e);
        }
    }

    public static <T> List<T> query(String tableName, Class<T> entityClass, ConditionBuilder conditionBuilder) {
        String sql = "SELECT * FROM " + tableName + " " + conditionBuilder.build();
        log.debug("查询SQL：{}", sql);
        return executeQuery(sql, entityClass, conditionBuilder.getParameters());
    }

    public static <T> List<T> queryByPage(String tableName, Class<T> entityClass,
                                          ConditionBuilder conditionBuilder, int pageNum, int pageSize) {
        String sql = "SELECT * FROM " + tableName + " " + conditionBuilder.build() +
                " LIMIT " + (pageNum - 1) * pageSize + ", " + pageSize;
        return executeQuery(sql, entityClass, conditionBuilder.getParameters());
    }

    public static <T> T queryOne(String tableName, Class<T> entityClass, ConditionBuilder conditionBuilder) {
        List<T> result = query(tableName, entityClass, conditionBuilder);
        return result.isEmpty() ? null : result.getFirst();
    }

    public static int delete(String tableName, ConditionBuilder conditionBuilder) {
        String sql = "DELETE FROM " + tableName + " " + conditionBuilder.build();
        try (Connection conn = DatabaseConfig.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            setParameters(stmt, conditionBuilder.getParameters());
            return stmt.executeUpdate();
        } catch (Exception e) {
            throw handleException("删除数据失败", e);
        }
    }

    public static <T> List<T> executeQuery(String sql, Class<T> entityClass, Object... params) {
        try (Connection conn = DatabaseConfig.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            setParameters(stmt, params);
            try (ResultSet rs = stmt.executeQuery()) {
                return resultSetToObject(rs, entityClass, true);
            }
        } catch (Exception e) {
            throw handleException("执行查询失败", e);
        }
    }

    public static int getCount(String tableName, ConditionBuilder conditionBuilder) {
        String sql = "SELECT COUNT(*) FROM " + tableName + " " + conditionBuilder.build();
        try (Connection conn = DatabaseConfig.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql);
             ResultSet rs = stmt.executeQuery()) {

            return rs.next() ? rs.getInt(1) : 0;
        } catch (Exception e) {
            throw handleException("获取数据数量失败", e);
        }
    }

    // ============ 私有工具方法 ============

    private static <T> String buildInsertSql(String tableName, T entity) {
        List<String> columns = new ArrayList<>();
        for (Field field : entity.getClass().getDeclaredFields()) {
            field.setAccessible(true);
            try {
                if (field.get(entity) != null) {
                    columns.add(StringUtil.convertUnderline(field.getName()));
                }
            } catch (IllegalAccessException ignored) {
            }
        }

        return "INSERT INTO " + tableName + " (" + String.join(", ", columns) +
                ") VALUES (" + String.join(", ", Collections.nCopies(columns.size(), "?")) + ")";
    }

    private static <T> String buildUpdateSql(String tableName, T entity, ConditionBuilder conditionBuilder) {
        List<String> sets = new ArrayList<>();
        for (Field field : entity.getClass().getDeclaredFields()) {
            field.setAccessible(true);
            try {
                if (field.get(entity) != null) {
                    sets.add(StringUtil.convertUnderline(field.getName()) + "=?");
                }
            } catch (IllegalAccessException ignored) {
            }
        }

        return "UPDATE " + tableName + " SET " + String.join(", ", sets) + " " + conditionBuilder.build();
    }

    private static <T> Object[] getNonNullFieldValues(T entity) {
        return Arrays.stream(entity.getClass().getDeclaredFields())
                .peek(f -> f.setAccessible(true))
                .filter(f -> {
                    try {
                        return f.get(entity) != null;
                    } catch (IllegalAccessException e) {
                        return false;
                    }
                })
                .map(f -> {
                    try {
                        return f.get(entity);
                    } catch (IllegalAccessException e) {
                        return null;
                    }
                })
                .filter(Objects::nonNull)
                .toArray();
    }

    private static void setParameters(PreparedStatement stmt, Object[] params) throws SQLException {
        AtomicInteger index = new AtomicInteger(1);
        Arrays.stream(params).forEach(param -> {
            try {
                stmt.setObject(index.getAndIncrement(), param);
            } catch (SQLException e) {
                throw new RuntimeException("设置参数失败", e);
            }
        });
    }

    private static <T> void setFieldValue(T entity, Field field, Object value, boolean ignoreNull) {
        try {
            if (value != null) {
                if (field.getType() == Date.class && value instanceof LocalDateTime) {
                    field.set(entity, Date.from(((LocalDateTime) value).atZone(java.time.ZoneId.systemDefault()).toInstant()));
                } else {
                    field.set(entity, value);
                }
            } else if (!ignoreNull) {
                field.set(entity, null);
            }
        } catch (IllegalAccessException e) {
            throw new RuntimeException("设置字段值失败", e);
        }
    }

    private static DatabaseException handleException(String message, Exception e) {
        log.error("{}，原因：{}", message, e.getMessage());
        return new DatabaseException(message + ": " + e.getMessage());
    }

    public static int getCountBySql(final String sql, final Object... params) {
        try (Connection conn = DatabaseConfig.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            setParameters(stmt, params);
            try (ResultSet rs = stmt.executeQuery()) {
                return rs.next() ? rs.getInt(1) : 0;
            }
        } catch (Exception e) {
            throw handleException("获取数据数量失败", e);
        }
    }
}
